1. Field of the Invention
The present invention relates to systems and methods for analytically modeling data organized and stored in a relational database, and, more particularly, to analytically modeling data organized according to a referenced attribute.
2. Description of the Prior Art
Online analytical processing (OLAP) is a key part of many data warehouse and business analysis systems. OLAP services provide for fast analysis of multidimensional information. For this purpose, OLAP services provide for multidimensional access and navigation of data in an intuitive and natural way, providing a global view of data that can be drilled down into particular data of interest. Speed and response time are important attributes of OLAP services that allow users to browse and analyze data online in an efficient manner. Further, OLAP services typically provide analytical tools to rank, aggregate, and calculate lead and lag indicators for the data under analysis.
In this context, an OLAP cube may be modeled according to a user's perception of the data. The cube may have multiple dimensions, each dimension modeled according to attributes of the data. Typically, there is a hierarchy associated with each dimension. For example, a time dimension can consist of years subdivided into months subdivided into weeks subdivided into days, while a geography dimension can consist of countries subdivided into states subdivided into cities. Dimension members act as indices for identifying a particular cell or range of cells within the cube.
OLAP services are often used to analytically model data that is stored in a relational database such as, for example, an Online Transactional Processing (OLTP) database. Data stored in a relational database may be organized according to multiple tables with each table having data corresponding to a particular data type. A table corresponding to a particular data type may be organized according to columns corresponding to data attributes. For example, data corresponding to the type “Customer” may be organized in a “Customer” table with columns “Customer ID” and “Location ID”. Data corresponding to the type “Location” may be organized in a “Location” table with columns “Location ID”, “City”, and “State”.
The “Customer” table may be referred to as a “referencing” table, and the “Location” table may be referred to as a “referenced” table because data in the “Location” table is referenced by corresponding data in the “Customer” table. To obtain location data for a particular customer, the “Location ID” for the customer may be obtained based on the customer's “Customer ID” in the “Customer” table. Next, an entry having the obtained “Location ID” is found in the “Location” table based on the “Location ID” attribute thereof. Finally, from such found entry, the “State” and “City” are obtained for the “Customer ID” and customer.
One issue that arises with regard to analytically modeling data from a relational database is how to best model data with such a referenced table. In existing methods for analytically modeling data with a referenced table, a single dimension is modeled according to both a particular referencing table and a referenced table. The single dimension has a dimension hierarchy that includes attributes in the referencing table, which may be referred to as “referencing attributes” and attributes in the referenced table, which may be referred to as “referenced attributes”. For example, in such an existing method, an OLAP cube has a “Customer” dimension modeled according to both the “Customer” table and the “Location” table. The “Customer” dimension has a dimension hierarchy including “State” subdivided by “City” subdivided by “Customer ID”.
Including the referenced attributes in the dimension hierarchy of the single dimension is repetitive because multiple data entries corresponding to referenced attributes may have identical values. For example, with regard to the “Customer” and “Location” tables, multiple customers may reside in the same state and/or city. This repetition is exacerbated when multiple referencing tables refer to a single referenced table. This repetition would be eliminated if, rather than including referenced attributes in the dimension hierarchy of each dimension, referenced attributes were included only in the dimension hierarchy of a “referenced dimension” modeled according to the referenced table and referred to by the referencing dimension. Such elimination of repetition results in a reduction of data storage size and a reduction of the time required for modeling the cube.